package com.plsjavaweb.dao.implementation;

import java.util.List;

import org.apache.log4j.Logger;

import com.plsjavaweb.dao.DAOFactory;
import com.plsjavaweb.dao.interfaces.SearchDAO;
import com.plsjavaweb.dao.mapper.Mapper;
import com.plsjavaweb.dao.mapper.SearchMapper;
import com.plsjavaweb.dao.utilities.DAO;
import com.plsjavaweb.logging.LoggingConstants;
import com.plsjavaweb.pojo.SearchPOJO;

public class SearchDAOImpl implements SearchDAO {
	
	static Logger log= Logger.getLogger(DAOFactory.class);
	static LoggingConstants logConst = new LoggingConstants();
	
	@Override
	public List<SearchPOJO> validateEmployeeExists(String name) {
		log.info(logConst.entryMessage );
		
		StringBuilder query= new StringBuilder();
		query.append("Select E.first_name, E.last_name, S.seat_code, S.local_no from employee E ");
		query.append("left outer join employee_seat ES on E.employee_id = ES.employee_id ");
		query.append("inner join seat S on  ES.seat_id = S.seat_id ");
		query.append("left outer join resource R on R.employee_id = E.employee_id ");
		query.append("inner join project P on P.project_id = R.project_id ");
		
		
		boolean hasExclamationPoint = name.contains("!");	
		boolean hasDash = name.contains("-");	
		boolean hasPercentSign = name.contains("%");	
		boolean hasSquareBracket = validateSquareBracketExist(name);	
		boolean hasUnderline = name.contains("_");	
		
		
		//a
		if ((hasExclamationPoint == false) && 
				(hasDash == false) && 
				(hasPercentSign == false) && 
				(hasSquareBracket == false) && 
				(hasUnderline == false)) {
			query.append("where E.first_name = '" + name + "' or E.last_name = '" + name + "'");
			
		}
		
		//!a
		else if ((hasExclamationPoint == true) && 
				(hasDash == false) && 
				(hasPercentSign == false) && 
				(hasSquareBracket == false) && 
				(hasUnderline == false)) {
			
			name = name.replace("!","");
			query.append("where E.first_name NOT LIKE '" + name + "' and E.last_name NOT LIKE '" + name + "'");
			
		} 

		//!a%
		else if ((hasExclamationPoint == true) && 
				(hasDash == false) && 
				(hasPercentSign == true) && 
				(hasSquareBracket == false) && 
				(hasUnderline == false)) {
			name = name.replace("!","");
			query.append("where E.first_name NOT LIKE '" + name + "' and E.last_name NOT LIKE '" + name + "'");
		}
		
		//a%
		else if ((hasExclamationPoint == false) && 
				(hasDash == false) && 
				(hasPercentSign == true) && 
				(hasSquareBracket == false) && 
				(hasUnderline == false)) {
			
			query.append("where E.first_name LIKE '" + name + "' or E.last_name LIKE '" + name + "'");
			
		}
		
		//[a] [abc]
		else if ((hasExclamationPoint == false) && 
				(hasDash == false) && 
				(hasSquareBracket == true)){
			
			name = name.replace("[", "");
			name = name.replace("]", "");
			
			char[] nameArray = name.toCharArray();
			
			if (nameArray.length == 1) {
				query.append("where E.first_name LIKE '" + nameArray[0] + "%' or E.last_name LIKE '" + nameArray[0] + "%'");

			}
			
			else if (nameArray.length > 1) {
				
				query.append("where ");
				
				for (int i = 0; i < nameArray.length; i++) {
					if (i < nameArray.length - 1) {
						query.append("E.first_name LIKE '" + nameArray[i] + "%' or E.last_name LIKE '" + nameArray[i] + "%' or ");
					} else if (i == nameArray.length-1) {
						query.append("E.first_name LIKE '" + nameArray[i] + "%' or E.last_name LIKE '" + nameArray[i] + "%'");
					}
				}
			}
		}
		
		//[!a] [!abc]
		else if ((hasExclamationPoint == true) && 
				(hasDash == false) && 
				(hasSquareBracket == true)) {
			
			name = name.replace("[", "");
			name = name.replace("]", "");
			name = name.replace("!", "");
			
			char[] nameArray = name.toCharArray();
			
			if (nameArray.length == 1) {
				query.append("where E.first_name NOT LIKE '" + nameArray[0] + "%' and E.last_name NOT LIKE '" + nameArray[0] + "%'");
			}
			
			else if (nameArray.length > 1) {
				query.append("where ");
				
				for (int i = 0; i < nameArray.length; i++) {
					if (i < nameArray.length - 1) {
						query.append("E.first_name NOT LIKE '" + nameArray[i] + "%' and E.last_name NOT LIKE '" + nameArray[i] + "%' and ");
					} else if (i == nameArray.length-1) {
						query.append("E.first_name NOT LIKE '" + nameArray[i] + "%' and E.last_name NOT LIKE '" + nameArray[i] + "%'");
					}
				}
			}
		}

		//[!a-] [!a-c]
		else if ((hasExclamationPoint == true) && 
				(hasDash == true) && 
				(hasSquareBracket == true)) {
			
			name = name.replace("[", "");
			name = name.replace("]", "");
			name = name.replace("!", "");
			
			char[] nameArray = name.toCharArray();
			
			if (nameArray.length < 2) {
				nameArray[2] = 'z';
			}
				
			char firstLetter = nameArray[0];
			char lastLetter =nameArray[2];
			
			query.append("where E.first_name NOT LIKE '" + firstLetter + "%' and E.last_name NOT LIKE '" + firstLetter + "%' and ");

			for (char alphabet = firstLetter; alphabet < lastLetter; alphabet++) {
				query.append("E.first_name NOT LIKE '" + alphabet + "%' and E.last_name NOT LIKE '" + alphabet + "%' and ");
			}
			query.append("E.first_name NOT LIKE '" + lastLetter + "%' and E.last_name NOT LIKE '" + lastLetter + "%'");
		
		}
		

		//[a-] [a-c]
		else if ((hasExclamationPoint == false) && 
				(hasDash == true) && 
				(hasSquareBracket == true)) {
			
			name = name.replace("[", "");
			name = name.replace("]", "");
			name = name.replace("!", "");
			
			char[] nameArray = name.toCharArray();
			
			if (nameArray.length < 2) {
				nameArray[2] = 'z';
			}
				
			char firstLetter = nameArray[0];
			char lastLetter =nameArray[2];
			
			query.append("where E.first_name LIKE '" + firstLetter + "%' or E.last_name LIKE '" + firstLetter + "%' or ");

			for (char alphabet = firstLetter; alphabet < lastLetter; alphabet++) {
				query.append("E.first_name LIKE '" + alphabet + "%' or E.last_name LIKE '" + alphabet + "%' or ");
			}
			query.append("E.first_name LIKE '" + lastLetter + "%' or E.last_name LIKE '" + lastLetter + "%'");
		
		}

		else if ((hasExclamationPoint == true) && 
				(hasUnderline == true)) {
			name = name.replace("!","");
			query.append("where E.first_name NOT LIKE '" + name + "' and E.last_name NOT LIKE '" + name + "'");
		}
		
		//!a_
		else if ((hasExclamationPoint == false) && 
				(hasUnderline == true)) {
			
			query.append("where E.first_name LIKE '" + name + "' or E.last_name LIKE '" + name + "'");
		}

		else {
			query.append("where E.first_name = '' or E.last_name = ''");
		}

		List<SearchPOJO> searchList = null;
		try {
			DAO<SearchPOJO> dao = new DAO<>();
			Mapper<SearchPOJO> searchMapper=new SearchMapper();
					
			searchList = dao.ExecuteQuery(query.toString(), searchMapper);
	
		} catch (Exception e) {
			// TODO: handle exception
			log.info(logConst.errorMessage);
		}
		

		log.info(logConst.returnedMessage);
		return searchList;
	}
	
	@Override
	public List<SearchPOJO> validateEmployeeId(int employeeid) {
		log.info(logConst.entryMessage + "validateEmployeeId");
		
		StringBuilder query= new StringBuilder();
		query.append("Select E.first_name, E.last_name, S.seat_code, S.local_no from employee E ");
		query.append("left outer join employee_seat ES on E.employee_id = ES.employee_id ");
		query.append("inner join seat S on  ES.seat_id = S.seat_id ");
		query.append("left outer join resource R on R.employee_id = E.employee_id ");
		query.append("inner join project P on P.project_id = R.project_id ");
		query.append("where E.employee_id = '" + employeeid + "' or E.employee_id LIKE '%" + employeeid + "%' ");

		
		List<SearchPOJO> searchList = null;
		
		try {
			DAO<SearchPOJO> dao = new DAO<>();
			Mapper<SearchPOJO> searchMapper=new SearchMapper();
			
			searchList = dao.ExecuteQuery(query.toString(), searchMapper);
			
		} catch (Exception e) {
			// TODO: handle exception
			log.info(logConst.errorMessage);
		}
		
		log.info(logConst.returnedMessage);
		return searchList;
	}
	
	@Override
	public List<SearchPOJO> validateEmployeeResourceProject(String project) {
		log.info(logConst.entryMessage + "validateEmployeeResourceProject");
		
		StringBuilder query= new StringBuilder();
		query.append("Select E.first_name, E.last_name, S.seat_code, S.local_no from employee E ");
		query.append("left outer join employee_seat ES on E.employee_id = ES.employee_id ");
		query.append("inner join seat S on  ES.seat_id = S.seat_id ");
		query.append("left outer join resource R on R.employee_id = E.employee_id ");
		query.append("inner join project P on P.project_id = R.project_id ");
				
		boolean hasExclamationPoint = project.contains("!");	
		boolean hasDash = project.contains("-");	
		boolean hasPercentSign = project.contains("%");	
		boolean hasSquareBracket = validateSquareBracketExist(project);	
		boolean hasUnderline = project.contains("_");	
		
		
		//a
		if ((hasExclamationPoint == false) && 
				(hasDash == false) && 
				(hasPercentSign == false) && 
				(hasSquareBracket == false) && 
				(hasUnderline == false)) {
			query.append("where P.project_desc = '" + project + "'");
			
		}
		
		//!a
		else if ((hasExclamationPoint == true) && 
				(hasDash == false) && 
				(hasPercentSign == false) && 
				(hasSquareBracket == false) && 
				(hasUnderline == false)) {
			
			project = project.replace("!","");
			query.append("where P.project_desc NOT LIKE '" + project + "'");
			
		} 

		//!a%
		else if ((hasExclamationPoint == true) && 
				(hasDash == false) && 
				(hasPercentSign == true) && 
				(hasSquareBracket == false) && 
				(hasUnderline == false)) {
			project = project.replace("!","");
			query.append("where P.project_desc NOT LIKE '" + project + "'");
		}
		
		//a%
		else if ((hasExclamationPoint == false) && 
				(hasDash == false) && 
				(hasPercentSign == true) && 
				(hasSquareBracket == false) && 
				(hasUnderline == false)) {
			
			query.append("where P.project_desc LIKE '" + project + "' or E.last_name LIKE '" + project + "'");
			
		}
		
		//[a] [abc]
		else if ((hasExclamationPoint == false) && 
				(hasDash == false) && 
				(hasSquareBracket == true)){
			
			project = project.replace("[", "");
			project = project.replace("]", "");
			
			char[] projectArray = project.toCharArray();
			
			if (projectArray.length == 1) {
				query.append("where P.project_desc LIKE '" + projectArray[0] + "%'");

			}
			
			else if (projectArray.length > 1) {
				
				query.append("where ");
				
				for (int i = 0; i < projectArray.length; i++) {
					if (i < projectArray.length - 1) {
						query.append("P.project_desc LIKE '" + projectArray[i] + "%' or ");
					} else if (i == projectArray.length-1) {
						query.append("P.project_desc LIKE '" + projectArray[i] + "%'");
					}
				}
			}
		}
		
		//[!a] [!abc]
		else if ((hasExclamationPoint == true) && 
				(hasDash == false) && 
				(hasSquareBracket == true)) {
			
			project = project.replace("[", "");
			project = project.replace("]", "");
			project = project.replace("!", "");
			
			
			char[] projectArray = project.toCharArray();
			
			if (projectArray.length == 1) {
				query.append("where P.project_desc NOT LIKE '" + projectArray[0] + "%'");
			}
			
			else if (projectArray.length > 1) {
				query.append("where ");
				
				for (int i = 0; i < projectArray.length; i++) {
					if (i < projectArray.length - 1) {
						query.append("P.project_desc NOT LIKE '" + projectArray[i] + "%' and ");
					} else if (i == projectArray.length-1) {
						query.append("P.project_desc NOT LIKE '" + projectArray[i] + "%' ");
					}
				}
			}
		}

		//[!a-] [!a-c]
		else if ((hasExclamationPoint == true) && 
				(hasDash == true) && 
				(hasSquareBracket == true)) {
			
			project = project.replace("[", "");
			project = project.replace("]", "");
			project = project.replace("!", "");
			
			char[] projectArray = project.toCharArray();
			
			if (projectArray.length < 2) {
				projectArray[2] = 'z';
			}
				
			char firstLetter = projectArray[0];
			char lastLetter =projectArray[2];
			
			query.append("where P.project_desc NOT LIKE '" + firstLetter + "%' and ");

			for (char alphabet = firstLetter; alphabet < lastLetter; alphabet++) {
				query.append("P.project_desc NOT LIKE '" + alphabet + "%' and ");
			}
			query.append("P.project_desc NOT LIKE '" + lastLetter + "%'");
		
		}
		

		//[a-] [a-c]
		else if ((hasExclamationPoint == false) && 
				(hasDash == true) && 
				(hasSquareBracket == true)) {
			
			project = project.replace("[", "");
			project = project.replace("]", "");
			project = project.replace("!", "");
			
			char[] projectArray = project.toCharArray();
			
			if (projectArray.length < 2) {
				projectArray[2] = 'z';
			}
				
			char firstLetter = projectArray[0];
			char lastLetter =projectArray[2];
			
			query.append("where P.project_desc LIKE '" + firstLetter + "%' and ");

			for (char alphabet = firstLetter; alphabet < lastLetter; alphabet++) {
				query.append("P.project_desc LIKE '" + alphabet + "%' and ");
			}
			query.append("P.project_desc LIKE '" + lastLetter + "%'");
		
		}

		//a_
		else if ((hasExclamationPoint == false) && 
				(hasUnderline == true)) {
			query.append("where P.project_desc LIKE '" + project + "'");
		}

		//!a_
		else if ((hasExclamationPoint == true) && 
				(hasUnderline == true)) {
			project = project.replace("!","");
			query.append("where P.project_desc NOT LIKE '" + project + "'");
		}
		
		else {
			query.append("where P.project_desc = ''");
		}

		List<SearchPOJO> searchList = null;		
		try {
			
			DAO<SearchPOJO> dao = new DAO<>();
			Mapper<SearchPOJO> searchMapper=new SearchMapper();
			
			searchList = dao.ExecuteQuery(query.toString(), searchMapper);
			
		} catch (Exception e) {
			// TODO: handle exception
			log.info(logConst.errorMessage);
		}
		log.info(logConst.returnedMessage);
		return searchList;
	}

	public boolean validateSquareBracketExist(String word) {
		boolean squareBracketExists = false;
		
		if (word.contains("[")){
			squareBracketExists = true;
		} else if (word.contains("]")){
			squareBracketExists = false;
		}
		
		return squareBracketExists;
	}

}
